Le package dbExplorer

Un outil Shiny pour explorer les bases de données et les tables parquet

SSP/DEMESIS/BQIS/PAOS

14/05/2025

1 A quoi sert le package dbExplorer ?

Un package R qui lance une application Shiny qui a plusieurs objectifs :

Explorer rapidement les données présentes dans une base de données
Explorer rapidement le contenu des tables au format Parquet sur Cerise
Faire des manipulations de 1er niveau sur ces données

=> Un package développé et maintenu par Adam Marsal du SSM Justice.

2 Installation du package depuis Cerise

# install.packages("remotes")
remotes::install_git("https://gitlab.forge.agriculture.rie.gouv.fr/ssp/bmis/packages/dbexplorer",
                     dependencies = T,
                     git = "external")

Comme n’importe quel autre package R, {dbExplorer} doit être chargé avec :

library(dbExplorer)

Avant d’explorer ce package, 2 concepts théoriques sont nécessaires.

3 Les “Background jobs” de RStudio

Les “Background Jobs” dans RStudio sont une fonctionnalité qui permet d’exécuter des scripts R en arrière-plan sans bloquer l’interface principale de RStudio.

Quelques caractéristiques :

Une exécution indépendante : le job s’exécute dans un processus séparé de la session principale (non bloquant).
Suivi de l’exécution : une fenêtre dédiée dans RStudio qui permet de consulter la progression, les messages et l’état du job (traçabilité).
Une reproductibilité garantie : les jobs relancent le script tel qu’il est au moment de l’appel dans un environnement propre. Le job s’exécute de manière isolé sans polluer l’environnement global.

4 Exemple de Background jobs (1/3)

Soit le script simple suivant :

library(dplyr)

Sys.sleep(5)
tab_agregee <- iris |> 
  summarise(Moy_pet_long = mean(Petal.Length, na.rm = TRUE), .by = Species)
Sys.sleep(5)

print("Pgm terminé")

Ce script peut être lancé via un background job en cliquant sur le bouton "Start Background job".

5 Exemple de Background jobs (2/3)

La fenêtre suivante s’affiche :

6 Exemple de Background jobs (3/3)

En fonction des choix effectués au lancement du background job, les résultats seront (ou pas) disponibles dans l’environnement global de la session R.

Si vous avez choisi de stocker les résultats dans un objet “results”, voici comment y accéder :

7 Structure d’une base de données relationnelles

Les bases de données relationnelles sont structurées de manière hiérarchique et logique.

La Base de donnée (BDD) : c’est l’entité principale qui contient l’ensemble des données
Le schéma : c’est une structure logique à l’intérieur d’une BDD. Il sert à organiser les objets. Il peut être vu comme un dossier dans la BDD
Les tables : ce sont les éléments centraux où les données sont stockées
Les vues : ce sont des représentations virtuelles de tables (créées au moment des requêtes)

Exemple de structure :

Base de données : vente_en_ligne
│
├── Schéma : public
│   ├── Table : clients
│   ├── Table : commandes
│   └── Table : produits
│
└── Schéma : archive
    └── Table : commandes_2020

8 Se connecter à une BDD sans dbExplorer (1/3)

Exemple avec BDD PostgreSQL :

library(connections)
library(RPostgres)

### Chargement des infos de connection
infos_connection_prod <- read_delim("infos_connection.csv", 
                                    delim = ";", escape_double = FALSE, trim_ws = TRUE,
                                    show_col_types = FALSE) %>% 
  filter(ENV == "PROD")

### Connexion à la BDD
con <- connection_open(
    Postgres(), 
    user = infos_connection_prod[["USER"]],
    password = rawToChar(openssl::base64_decode(infos_connection_prod[["PWD"]])),
    host = infos_connection_prod[["DB_URL"]], 
    dbname = infos_connection_prod[["DATABASE"]],
    port = infos_connection_prod[["PORT"]])

9 Se connecter à une BDD sans dbExplorer (2/3)

=> La structure de la BDD est visible dans l’onglet "Connections" de RStudio :

10 Se connecter à une BDD sans dbExplorer (3/3)

11 Explorer une base de données PostgreSQL (1/4)

## Installation du package 
remotes::install_git("https://gitlab.forge.agriculture.rie.gouv.fr/ssp/bmis/packages/dbexplorer",
                     dependencies = T,
                     git = "external")

## Création du fichier de connexion à postgre SQL 
connector_file <- "./R/dbExplorer-connector/dbExplorerPgConnector.R"
dbExplorer:::createPostgreSQLConnector(host = "postgresql-164525.projet-sortie-sas",
                                       dbname = "dvf",
                                       port = 5432,
                                       file=connector_file)

## Lancement de l'application sur ce fichier de connexion. 
ExplorerDonnees(connectorFile = connector_file)

=> Un “background job” se lance dans RStudio et une application shiny s’ouvre dans le navigateur.

12 Explorer une base de données PostgreSQL (2/4)

Entrer le login & mot de passe d’accès à la BDD :

13 Explorer une base de données PostgreSQL (3/4)

On peut aussi supprimer la fenêtre de login en modifiant le fichier dbExplorerPgConnector.R.

14 Explorer une base de données PostgreSQL (4/4)

15 Démonstration

Démo sur Cerise ! (voir rép. dbExplorer_test)

16 Quelques raccourcis/astuces utiles !

Maintenir la touche “Ctrl” enfoncée pour sélectionner les colonnes interactivement
Pour filtrer les lignes, cocher la case “Cliquer pour filtrer” puis :
- Cliquer une fois pour appliquer le filtre sur les lignes de la table
- Cliquer deux fois pour appliquer la négation du filtre sur les lignes de la table
Pour télécharger les jeux de données au format csv, cliquer en haut à droite sur le bouton “télécharger” ou faire Ctrl + S.
Appuyer sur le bouton “+” pour travailler sur différents onglets (pour différentes tables).

17 Création de fichiers parquet à partir d’une BDD (1/2)

### Connexion à la BDD
con <- connection_open(
    Postgres(), 
    user = infos_connection_prod[["USER"]],
    password = rawToChar(openssl::base64_decode(infos_connection_prod[["PWD"]])),
    host = infos_connection_prod[["DB_URL"]], 
    dbname = infos_connection_prod[["DATABASE"]],
    port = infos_connection_prod[["PORT"]])

Appel de la fonction exportPgToParquet.R :

source("R/exportPgToParquet.R")

18 Création de fichiers parquet à partir d’une BDD (2/2)

# Export de la table categorie_juridique
exportPgToParquet(con,"categorie_juridique")
# Export de la table entite_surface
exportPgToParquet(con,"entite_surface")

19 Explorer une base de données SQLite

library(RSQLite)
connector_file <- "R/dbExplorer-connector/connector_sqlite.R"
dbExplorer::ExplorerDonnees(connectorFile = "R/dbExplorer-connector/connector_sqlite.R")

20 Explorer des fichiers parquets

dbExplorer::ExplorerDossiers("~/CERISE/03-Espace-de-Diffusion/030_Structures_exploitations/3020_Recensements/RA_2010/")

21 Quelques compléments sur les manipulations de BDD avec R (1/2)

Prenons une base SQLite :

Connexion à la BDD

library(RSQLite)

chemin_IPPAP_dev <- "~/CERISE/02-Espace-de-Production/090_Prix/9010_IPPAP/IPPAP_app/dev/App/donnees/IPPAP.sqlite"
db_IPPAP <- dbConnect(RSQLite::SQLite(), dbname = chemin_IPPAP_dev)

Quelques manipulations possibles avec des fonctions R :

Pour afficher la liste des tables de la base de donnees

dbListTables(db_IPPAP)

> [1] "COEFF_RACCORDEMENT"   "DATES"                "NOMENCLATURE_BDC"    
[4] "POND_MOIS_COMPOSANTS" "PRIX"                 "QUANTITES"           
[7] "REFERENTIEL_IPPAP"    "VIVANTES"

22 Quelques compléments sur les manipulations de BDD avec R (2/2)

Pour afficher les colonnes d’une table

dbListFields(db_IPPAP,"PRIX")
> [1] "CODE_S"     "2019-01-01" "2019-02-01" "2019-03-01" "2019-04-01" "2019-05-01"
  [7] "2019-06-01" "2019-07-01" "2019-08-01" "2019-09-01" "2019-10-01" "2019-11-01"
 [13] "2019-12-01" "2020-01-01" "2020-02-01" "2020-03-01" "2020-04-01" "2020-05-01"
 [19] "2020-06-01" "2020-07-01" "2020-08-01" "2020-09-01" "2020-10-01" "2020-11-01"
 ...

Pour lire une table

verif_PRIX <- dbReadTable(db_IPPAP,"PRIX")
head(verif_PRIX)[,1:4]
    CODE_S  2019.01.01  2019.02.01  2019.03.01  2019.04.01
1 10100046       1.839       1.794       1.700       1.729
2  1120057          NA          NA       0.606       0.516
3  1140001          NA          NA          NA          NA
4  1150001          NA          NA          NA          NA

23 Utilisation basique du package {dm}

Installation et chargement :

install.packages('dm')
library(dm)

Création d’un objet dm :

fin_dm <- dm_from_src(db_IPPAP)
fin_dm

── Table source ───────────────────────────────────────────────────────────────────────────
src:  sqlite 3.47.1 [/var/data/nfs/CERISE/02-Espace-de-Production/090_Prix/9010_IPPAP/IPPAP_app/dev/App/donnees/IPPAP.sqlite]
── Metadata ───────────────────────────────────────────────────────────────────────────────
Tables: `COEFF_RACCORDEMENT`, `DATES`, `NOMENCLATURE_BDC`, `POND_MOIS_COMPOSANTS`, `PRIX`, … (8 total)
Columns: 511
Primary keys: 0
Foreign keys: 0

24 Aperçu de quelques fonctions de {dm} (1/2)

Pour afficher la liste des tables de la base de donnees

names(fin_dm)

> [1] "COEFF_RACCORDEMENT"   "DATES"                "NOMENCLATURE_BDC"    
[4] "POND_MOIS_COMPOSANTS" "PRIX"                 "QUANTITES"           
[7] "REFERENTIEL_IPPAP"    "VIVANTES"

Pour lire une table

fin_dm$PRIX
# Source:   table<`PRIX`> [?? x 121]
# Database: sqlite 3.47.1 [/var/data/nfs/CERISE/02-Espace-de-Production/090_Prix/9010_IPPAP/IPPAP_app/dev/App/donnees/IPPAP.sqlite]
   CODE_S   `2019-01-01` `2019-02-01` `2019-03-01` `2019-04-01` `2019-05-01` `2019-06-01` `2019-07-01` `2019-08-01`
   <chr>           <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
 1 10100046        1.84         1.79         1.7          1.73         1.75        NA           NA           NA    
 2 1120057        NA           NA            0.606        0.516        0.504        0.531        0.75         0.657
 3 1140001        NA           NA           NA           NA            0.162        0.295        0.208        0.203

25 Aperçu de quelques fonctions de {dm} (2/2)

Pour afficher les colonnes d’une table

colnames(fin_dm$PRIX)
> [1] "CODE_S"     "2019-01-01" "2019-02-01" "2019-03-01" "2019-04-01" "2019-05-01"
  [7] "2019-06-01" "2019-07-01" "2019-08-01" "2019-09-01" "2019-10-01" "2019-11-01"
 [13] "2019-12-01" "2020-01-01" "2020-02-01" "2020-03-01" "2020-04-01" "2020-05-01"
 [19] "2020-06-01" "2020-07-01" "2020-08-01" "2020-09-01" "2020-10-01" "2020-11-01"
 ...

Pour faire des comptages sur une table

dplyr::count(fin_dm$REFERENTIEL_IPPAP,LIB_PRODUIT)
# Source:   SQL [?? x 2]
# Database: sqlite 3.47.1 [/var/data/nfs/CERISE/02-Espace-de-Production/090_Prix/9010_IPPAP/IPPAP_app/dev/App/donnees/IPPAP.sqlite]
   LIB_PRODUIT             n
   <chr>               <int>
 1 Abricot                12
 2 Ail                     1
 3 Artichaut               7
# ℹ more rows
# ℹ Use `print(n = ...)` to see more rows

26 Visualiser les relations entre les tables avec {dm}

Exemple issu de la documentation avec dm_draw()…

dm %>%
  dm_draw()

… qui permet de visualiser les relations entre les tables :

Le package {dm} est très riche ! D’autres fonctionnalités pourront être abordés lors d’un futur GUR !

27 Pour en savoir plus

Package dm
Fiche utilitr sur l’utilisation des bases de données avec R.